# !/usr/bin/python
# -*- coding:UTF-8 -*-

from plan_util import *


class Export_Ranking(Util):
    def __init__(self, host='', username='', password='', media='', pthost="", charset='utf8'):
        self.dbconn = None
        self.cursor = None

        self.host = host
        self.username = username
        self.password = password
        self.charset = charset
        self.media = media
        self.pthost = pthost
        self.flag = True
        self.size = 200
        self.workbooks = None
        self.sheets = None

        self.user = {}  # 设计师
        self.signArea = {}  # 设计大区
        self.signUnit = {}  # 设计部

        self.smuser = {}  # 业务员
        self.smSignArea = {}  # 业务大区
        self.smSignUnit = {}  # 业务部

        self.colum = u"""
                db.rate AS rate,
                dr.name AS defaname,
                dd.name AS dename,
                du.userName AS username,
                db.rate * d.orderTotalPrice AS price,
                du.userid AS userId
            """

        self.SQL_Order_Signing_Informations = u""" 
            SELECT 
                %s
            FROM
                decoration.decoration d,
                communion.user du,
                decoration.decorationbelong db,
                communion.department dd,
                communion.department dr
            WHERE
                d.orderId = db.orderId
                    AND d.%s = du.uid
                    AND du.did = dd.did
                    AND dd.fdid = dr.did
                    AND d.company = %s
                    AND db.type = %s
                    AND d.status = 2
                    AND db.status = 0
                    AND d.contractTime >= '%s 00:00:00'
                    AND d.contractTime <= '%s 23:59:59'
        """

    # 创建表格返回sheet页
    def createWorkbookAndSheet(self, path):
        workbookParams = self.createWorkbook(u"设计部门排名")
        self.workbooks = workbookParams[0]
        table1 = ["排名", "部门", "分区", "签单数量", "签单额"]  # 设计部门排名
        table2 = ["排名", "分区", "签单数量", "签单额"]  # 设计分区排名
        table3 = ["排名", "设计师", "部门名称", "分区名称", "签单数量", "签单额"]  # 设计师排名

        sheet1 = workbookParams[1]
        sheet2 = self.workbooks.add_sheet(u"设计分区排名", cell_overwrite_ok=True)
        sheet3 = self.workbooks.add_sheet(u"设计师排名", cell_overwrite_ok=True)
        sheet4 = self.workbooks.add_sheet(u"业务部门排名", cell_overwrite_ok=True)
        sheet5 = self.workbooks.add_sheet(u"业务分区排名", cell_overwrite_ok=True)
        sheet6 = self.workbooks.add_sheet(u"业务员排名", cell_overwrite_ok=True)

        self.createTableHead(sheet1, table1)
        self.createTableHead(sheet2, table2)
        self.createTableHead(sheet3, table3)
        self.createTableHead(sheet4, table1)
        self.createTableHead(sheet5, table2)
        table3[1] = "业务员"
        self.createTableHead(sheet6, table3)

        self.saveWorkbook(path, self.workbooks)

        return sheet1, sheet2, sheet3, sheet4, sheet5, sheet6

    # 排序数据
    def assortDate(self, v, type):
        rate = float(v[0])  # 比例,签单数
        RegionName = v[1]  # 大区ID名称
        DeptName = v[2]  # 部门名称
        userName = v[3]
        price = float(v[4])
        userId = v[5]

        signUnit = (DeptName, RegionName, rate, price)  # 部门
        # signUnitsss = (rate, RegionName, DeptName, userName, price, userId)  # 部门
        signArea = (RegionName, rate, price)  # 大区
        user = (userName, DeptName, RegionName, rate, price)  # 设计师

        smSignUnit = (DeptName, RegionName, rate, price)  # 部门 3
        smSignArea = (RegionName, rate, price)  # 大区 2
        smuser = (userName, DeptName, RegionName, rate, price)  # 4

        if type == 0:
            if not self.user.has_key(userName):
                self.user[userName] = user
            else:
                user = list(self.user[userName])
                sumRate = user[3] + rate
                sumPrice = user[4] + price
                self.user[userName] = (userName, DeptName, RegionName, sumRate, sumPrice)

            # 大区
            if not self.signArea.has_key(RegionName):
                self.signArea[RegionName] = signArea
            else:
                signArea = list(self.signArea[RegionName])
                sumRate = signArea[1] + rate  # 签单数量签单额
                sumPrice = signArea[2] + price
                self.signArea[RegionName] = (RegionName, sumRate, sumPrice)
            # 部门排名
            if not self.signUnit.has_key(DeptName):
                self.signUnit[DeptName] = signUnit
            else:
                signUnit = list(self.signUnit[DeptName])
                sumRate = signUnit[2] + rate  # 签单数量签单额
                sumPrice = signUnit[3] + price
                self.signUnit[DeptName] = (DeptName, RegionName, sumRate, sumPrice)
        else:
            if not self.smuser.has_key(userId):
                self.smuser[userId] = smuser
            else:
                smuser = list(self.smuser[userId])
                sumRate = smuser[3] + rate
                sumPrice = smuser[4] + price
                self.smuser[userId] = (userName, DeptName, RegionName, sumRate, sumPrice)

            # 大区
            if not self.smSignArea.has_key(RegionName):
                self.smSignArea[RegionName] = smSignArea
            else:
                smSignArea = list(self.signArea[RegionName])
                sumRate = smSignArea[1] + rate  # 签单数量签单额
                sumPrice = smSignArea[2] + price
                self.smSignArea[RegionName] = (RegionName, sumRate, sumPrice)

            # 部门排名
            if not self.smSignUnit.has_key(DeptName):
                self.smSignUnit[DeptName] = smSignUnit
            else:
                smSignUnit = list(self.signUnit[DeptName])
                sumRate = smSignUnit[2] + rate  # 签单数量签单额
                sumPrice = smSignUnit[3] + price
                self.smSignUnit[DeptName] = (DeptName, RegionName, sumRate, sumPrice)

    def wirteSheetByAssortDate(self, path):

        listDate = [self.signUnit, self.signArea, self.user, self.smSignUnit, self.smSignArea, self.smuser]
        sortRuler = [3, 2, 4, 3, 2, 4]
        # assortDate = {
        #     3: self.signUnit,
        #     2: self.signArea,
        #     4: self.user,
        #     3: self.smSignUnit,
        #     2: self.smSignArea,
        #     4: self.smuser
        # }
        # for (key, value) in assortDate.items():
        #     print key
        for i, v in enumerate(self.sheets):
            listDate[i] = listDate[i].values()
            listDate[i] = sorted(listDate[i], key=lambda ruler: ruler[sortRuler[i]], reverse=True)

            portraits = 1
            money = 0
            num = 0
            for j, k in enumerate(listDate[i]):
                money += k[len(k) - 1]
                num += k[len(k) - 2]
                self.createTableHead(v, (portraits,) + k, portraits)
                portraits = portraits + 1

            alignment = xlwt.Alignment()
            alignment.horz = xlwt.Alignment.HORZ_CENTER
            style = xlwt.XFStyle()  # 创建样式
            style.alignment = alignment

            v.write_merge(portraits, portraits, 0, sortRuler[i] - 1, u"合计", style)
            # v.write_merge(portraits, portraits, 0, sortRuler[i]-1, u"合计")
            self.writeWorkbook(v, portraits, sortRuler[i], str(money))
            self.writeWorkbook(v, portraits, sortRuler[i] + 1, str(num))

    def main(self, pid, args, company):
        self.getDBCursor(self.host, self.username, self.password, self.charset)

        path = tempfile.gettempdir() + u"/Signing.xls"
        self.sheets = self.createWorkbookAndSheet(path)
        type = {
            0: u"dsuid",
            1: u"smuid"
        }
        startTime, endTime = args.split("-")
        # print startTime, endTime
        sql1 = self.SQL_Order_Signing_Informations % ("count(*)", "dsuid", company, 0, startTime, endTime)
        sql2 = self.SQL_Order_Signing_Informations % ("count(*)", "smuid", company, 1, startTime, endTime)
        countAll = self.count(sql1) + self.count(sql2)

        for i, v in enumerate(type):
            sqls = self.SQL_Order_Signing_Informations % (self.colum, type[i], company, i, startTime, endTime)
            flags = True
            nowPage = 1
            while (flags):
                data = self.page(sqls, nowPage, self.size)
                for j, k in enumerate(data):
                    self.assortDate(k, v)  # 整理数数据准备排名

                nowPage = nowPage + 1
                if len(data) < self.size:
                    flags = False

        self.wirteSheetByAssortDate(path)
        self.saveWorkbook(path, self.workbooks)

        # self.readFilesByline(path)

        print path


if __name__ == '__main__':
    """
    导出款项脚本说明:
        根据查询条件,以分页拿数据的方式导出款项;并上传到文件服务器中,可从任务页面上点击下载即可;
        当该脚本执行出现异常时,将跟新进度为失败并记录到log文件中上传至文件服务器中;
    1.传入必须参数说明
        1.pid         information.plan的主键;
        2.args        ""

    2.该脚本主要方法简介:
        封装了POST请求
            @功能：封装post方式;
    　　    @paramType:指传入参数类型，可以是form-data、x-www-form-urlencode、json;
        提供上传,下载, 文件的创建,写入,删除功能;
        本个脚本,封装了常用的关于xls文件的创建,写入,保存方法;
        提供创建表头(createTableHead方法)传入sheet和tableHead  tableHead为列表;
        提供分页拿数据page方法 传入将要执行的SQL,和当前页,容量默认为50 注:传入SQL中不能出现 ";"号,因本方法在传入SQL此基础上增加limit;
    """
    modernFilePath = os.path.realpath(__file__)
    configPath = "%s/%s" % (os.path.dirname(modernFilePath), "config.json")

    config = json.load(open(configPath))
    dbconf = config.get("database")

    # pid = sys.argv[1]
    # args = sys.argv[2]
    # company = sys.argv[3]

    pid = 123
    args = "2017/10/17-2018/12/12"
    company = "1"

    export_ranking = Export_Ranking(dbconf.get("host"), dbconf.get("username"), dbconf.get("password"),
                                    config.get("media"), config.get("pthost"))
    export_ranking.main(pid, args, int(company))
